package com.dtt.company.department.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.dtt.company.department.model.DepartmentModel;
import com.dtt.company.util.JDBCUtil;

public class DepartmentDao {
	
	///biangeng------

	private String table="department";
	private Connection conn=null;
	private PreparedStatement ps=null;
	
	public Integer insert(DepartmentModel model) {
		StringBuffer sql=new StringBuffer("insert into ");
//		sql.append(table).append("(code,name,phone,num)").append("value(?,?,?,?)");
		sql.append(table).append("(code,name,phone)").append("value(?,?,?)");
		conn=JDBCUtil.getConnection();
		try {
			ps=conn.prepareStatement(sql.toString());
			ps.setString(1, model.getCode());
			ps.setString(2, model.getName());
			ps.setString(3, model.getPhone());
//			ps.setInt(4, model.getNum());
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	public Integer delete(DepartmentModel model) {
		StringBuffer sql=new StringBuffer("delete from ");
		sql.append(table);
		List<Object> vals=appendWhere(sql,model);
		conn=JDBCUtil.getConnection();
		try {
			ps=conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size();i++) 
				ps.setObject(i+1, vals.get(i));				
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	public Integer update(DepartmentModel model) {
		StringBuffer sql=new StringBuffer("update ");
		sql.append(table);
		List<Object> vals=appendUpdate(sql,model);
		conn=JDBCUtil.getConnection();
		try {
			ps=conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size();i++) 
				ps.setObject(i+1, vals.get(i));				
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	
	public List<DepartmentModel> select(DepartmentModel model){
		StringBuffer sql=new StringBuffer
			("select code,name,phone,(select count(id) from employee where dep_code=department.code) num from ");
		sql.append(table);
		List<Object> vals=appendWhere(sql,model);
		List<DepartmentModel> list=new ArrayList<>();
		conn=JDBCUtil.getConnection();
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size();i++) {
				ps.setObject(i+1, vals.get(i));	
//				System.out.println(vals.get(i));
			}				
//			System.out.println(sql.toString());
			rs= ps.executeQuery();
			while(rs.next()) {
				DepartmentModel md=new DepartmentModel();
				md.setCode(rs.getString("code"));
				md.setName(rs.getString("name"));
				md.setPhone(rs.getString("phone"));
				md.setNum(rs.getInt("num"));
				list.add(md);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps,rs);
		}
		return list;
		
	}
	
	public int selectCount(DepartmentModel model){
		StringBuffer sql=new StringBuffer("select count(id) from ");
		sql.append(table);
		List<Object> vals=appendWhere(sql,model);
		List<DepartmentModel> list=new ArrayList<>();
		conn=JDBCUtil.getConnection();
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size();i++) {
				ps.setObject(i+1, vals.get(i));	
//				System.out.println(vals.get(i));
			}				
//			System.out.println(sql.toString());
			rs= ps.executeQuery();
			if(rs.next())
				return rs.getInt(1);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps,rs);
		}
		return 0;
		
	}
	
	public List<Object> appendWhere(StringBuffer sql, DepartmentModel model) {
		sql.append(" where 1=1");
		List<Object> objs=new ArrayList<Object>();
		String code=model.getCode();
		if(code!=null&&!code.trim().isEmpty()) {//!"".equals(code.trim())
			sql.append(" and code like ?");
			objs.add(code);
		}		
		String name=model.getName();
		if(name!=null&&!name.trim().isEmpty()) {
			sql.append(" and name like ?");
			objs.add(name);
		}
		String phone=model.getPhone();
		if(phone!=null&&!phone.trim().isEmpty()) {
			sql.append(" and phone like ?");
			objs.add(phone);
		}	
		String order=model.getOrderby();
		if(order!=null&&!order.trim().isEmpty()) {
			sql.append(" order by ").append(order);
			//不能用？，因为？上的数据带引号
		}	
		if(model.isPageOn()) {
			sql.append(" limit ?,? ");
			objs.add(model.getRowStart());
			objs.add(model.getPageLimit());//objs.add(model.getRowCount());
		}
		return objs;
	}
	public List<Object> appendUpdate(StringBuffer sql,DepartmentModel model ){
		List<Object> objs=new ArrayList<Object>();
		sql.append(" set id=id");		
		String name=model.getName();
		if(name!=null) {
			sql.append(",name=?");
			objs.add(name);
		}
		String phone=model.getPhone();
		if(phone!=null) {
			sql.append(",phone=?");
			objs.add(phone);
		}
//		Integer num=model.getNum();
//		if(num!=null) {
//			sql.append(",num=?");
//			objs.add(num);
//		}
		String code=model.getCode();
		sql.append(" where code=?");
		objs.add(code);
		return objs;
	}
	public static void main(String[] args) {
		DepartmentModel model=new DepartmentModel();
		model.setCode("111");
		model.setName("人力资源部");
		model.setPhone("3574587");
//		model.setNum(11);
		DepartmentDao dao=new DepartmentDao();
		Integer r=dao.insert(model);
//		Integer r=dao.update(model);
//		List<DepartmentModel> list=dao.select(model);
//		Integer r=dao.delete(model);
		System.out.println(r);
//		if(list!=null) {
//			for(DepartmentModel li:list) {
//				System.out.println(li.toString());
//			}
//		}
//		else System.out.println("空");
		
	}
}
